<HTML><HEAD><TITLE>session_sql_query(++Session, +ResultTemplate, ++SQLQuery, ++Options, -Cursor)</TITLE>
</HEAD><BODY>[ <A HREF="index.html">library(dbi)</A> | <A HREF="../../index.html">Reference Manual</A> | <A HREF="../../fullindex.html">Alphabetic Index</A> ]
<H1>session_sql_query(++Session, +ResultTemplate, ++SQLQuery, ++Options, -Cursor)</H1>
Executes a SQL query on the database server with options specified by Options.
<DL>
<DT><EM>Session</EM></DT>
<DD>A session handle
</DD>
<DT><EM>ResultTemplate</EM></DT>
<DD>Template defining the types of results tuple (structure)
</DD>
<DT><EM>SQLQuery</EM></DT>
<DD>A SQL statement query (string)
</DD>
<DT><EM>Options</EM></DT>
<DD>Options (list of Option:Value pairs or nil)
</DD>
<DT><EM>Cursor</EM></DT>
<DD>Returned cursor handle
</DD>
</DL>
<H2>Description</H2>
<P>
 Executes a SQL query on the database server. The predicate returns in
 Cursor the cursor handle for this SQL query, and the results can then be
 retrieved using cursor_*_tuple family of predicates. Options is 
 a (possibly empty) list of <TT>Option:Value</TT> pairs, specifying 
 DBMS-specific options for the cursor.
</P><P>
 The SQL query returns result in tuples of N elements each. Each tuple is
 mapped to a Prolog structure of arity N. ResultTemplate is a structure of
 arity N specifying the types of the return data for ECLiPSe. See the
 general description of this library or the manual for a description of 
 the template specification.
 </P><P>
 The SQL query must be valid for the DBMS to execute. It can contain
 NULL characters, i.e. it can contain binary data.
</P><P>
 MySQL specific:
</P><P>
 Options is used to specify the type of cursor used. Currently this only
 applies to cursors for SQL queries. The options are:
<DL>
<P>
<DT><STRONG><TT>buffering</TT></STRONG>
<DD>Specifies where the result set of a SQL query is buffered. Value can be
 either <TT>client</TT> (the default) or <TT>server</TT>. By default, the
 whole of the result set for a query is copied to the client (i.e. the
 ECLiPSe process running lib(dbi)) after the SQL query is executed. The 
 alternative is to leave the result set on the DBMS server, and only get
 the result tuples from the server one by one (with e.g. cursor_next_tuple/2).
 </P><P>
 The default buffering is on the client side, because this is the default
 of the MySQL C API, and in addition, it imposes certain restrictions on
 how the result tuples can be retrieved. However, as the whole result set
 is retreived, this can impose significant memory overheads if there are
 many tuples in the result set. On the other hand, there is no restrictions
 on how many active client buffered cursor is allowed per session at the
 same time, but only one active server buffered cursor is allowed at any
 one time -- a query result must be exhausted or the cursor explicitly
 closed before another query can be issued for that session.
 </P><P>
<DT><STRONG><TT>type</TT></STRONG>
<DD>This option is not relevant for the direct SQL queries of
 session_sql_query/4. It is only relevant for prepared queries, and has no
 effect here.
</DL>

<H3>Exceptions</H3>
<DL>
<DT><EM>(5) type error </EM>
<DD>Session is not a valid session handle, or SQLQuery not a string, or ResultTemplate not a structure
<DT><EM>(6) out of range </EM>
<DD>Invalid option specification in Options
<DT><EM>(dbi_error) </EM>
<DD>Error from DBMS while executing SQLQuery.
<DT><EM>(dbi_bad_template) </EM>
<DD>ResultTemplate has the wrong arity
</DL>
<H2>Examples</H2>
<PRE>
  check_overdraft_limit(Session, Account) :-
      L = ["select count(id) from accounts \
          where     id = ",Account," and balance &lt; overdraft"],
      concat_string(L,SQL),
      % the buffering:server option is MySQL specific
      session_sql_query(Session,c(0),SQL,[buffering:server],OverdraftCheck),
      cursor_next_tuple(OverdraftCheck,c(Count)),
      Count = 0.
</PRE>
<H2>See Also</H2>
<A HREF="../../lib/dbi/session_sql_query-4.html">session_sql_query / 4</A>, <A HREF="../../lib/dbi/cursor_next_tuple-2.html">cursor_next_tuple / 2</A>, <A HREF="../../lib/dbi/cursor_all_tuples-2.html">cursor_all_tuples / 2</A>, <A HREF="../../lib/dbi/cursor_N_tuples-4.html">cursor_N_tuples / 4</A>, <A HREF="../../lib/dbi/session_sql_prepare_query-5.html">session_sql_prepare_query / 5</A>, <A HREF="../../lib/dbi/cursor_close-1.html">cursor_close / 1</A>
</BODY></HTML>
